1 使用聚集函数汇总数据（AVG、COUNT、MAX、MIN、SUM）

我们经常需要汇总数据而不用把它们都检索出来。比如：

• 确定表中行数（或者满足某个条件或包含某个特定值的行数）。
• 获得表中某些行的和。
• 找出表列（或所有行或者某些特定行）的最大值、最小值、平均值。

聚集函数（aggregate function）：对某些行运行的函数，计算并返回一个值。

SQL给了5个聚集函数。

1.1 AVG()函数

AVG()函数通过对表中行数计数并计算其列值之和，求得该列的平均值。AVG()可用来返回所有列的平均值，也可以返回特定列或行的平均值。

计算Products表中所有产品的平均价格：

SELECT AVG(prod_price) AS avg_price 
FROM Products;

计算特定供应商DLL01所提供产品的平均价格：

SELECT AVG(prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';

注意：AVG()只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出。为了获得多个列的平均值，必须使用多个AVG()函数，如AVG(num1)、AVG(num2)、AVG(num3)等。

AVG()函数在计算时，会忽略列值为NULL的行。

1.2 COUNT()函数

COUNT()函数进行计数，可确定表中行的数目或符合特定条件的行的数目。

COUNT()函数有两种使用方式：

1. 使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值NULL还是非空值。
2. 使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。

计算Customers表中顾客的总数：

SELECT COUNT(*) AS num_cust 
FROM Customers;

只计算有Email的顾客：

SELECT COUNT(cust_email) AS num_cust 
FROM Customers;

1.3 MAX()函数

MAX()函数返回指定列中的最大值，要求必须指定列名。

计算Products表中最贵物品的价格：

SELECT MAX(prod_price) AS max_price 
FROM Products;

虽然MAX()一般用来找出最大的数值或日期值，但许多DBMS允许将它用来返回任意列中的最大值，包括返回文本列中的最大值。用于文本数据时，MAX()返回按该列排序后的最后一行。

MAX()函数会忽略列值为NULL的行。

1.4 MIN()函数

MIN()函数返回指定列中的最小值，要求必须指定列名。

计算Products表中最便宜物品的价格：

SELECT MIN(prod_price) AS min_price 
FROM Products;

虽然MIN()一般用来找出最小的数值或日期值，但许多DBMS允许将它用来返回任意列中的最小值，包括返回文本列中的最小值。用于文本数据时，MIN()返回按该列排序后最前面的行。

MIN()函数会忽略列值为NULL的行。

1.5 SUM()函数

SUM()函数用来返回指定列值的和（总计）。

计算订单号为20005的所购物品的总数：

SELECT SUM(quantity) AS items_ordered
FROM OrderItems 
WHERE order_num = 20005;

计算订单号为20005的所购物品，花费的总金额：

SELECT SUM(item_price * quantity) AS total_price
FROM OrderItems 
WHERE order_num = 20005;

SUM()函数会忽略列值为NULL的行。

1.6 聚集不同的值

• 对所有行执行计算，指定ALL参数或者不指定参数（ALL是默认行为）。
• 只包含不同的值，指定DISTINCT参数。

计算特定供应商DLL01提供的产品的不同价格的平均值，也就是排除相同价格的产品。

SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products 
WHERE vend_id = 'DLL01';

在使用了DISTINCT之后，发现avg_price比较高，是因为有多个产品具有相同的较低价格，排除它们，提高了平均价格。

注意：如果指定列名，则DISTINCT只能用于COUNT()。DISTINCT不能用于COUNT(*)。DISTINCT必须使用列名，不能用于计算或表达式。

有些DBMS支持对查询结果子集进行计算的TOP和TOP PERCENT。

1.7 组合聚集函数

计算Products表中物品的数目，产品价格的最大值、最小值以及平均值。

SELECT COUNT(*) AS num_items,
       MIN(prod_price) AS price_min,
       MAX(prod_price) AS price_max,
       AVG(prod_price) AS price_avg
FROM Products;

2 分组数据（GROUP BY、HAVING）

2.1 创建分组（GROUP BY）

SELECT vend_id, COUNT(*) AS num_prods
FROM Products 
GROUP BY vend_id;

在使用GROUP BY子句前，需要知道一些重要的规定。

1. GROUP BY子句可以包含任意数目的列，因而可以对分组进行嵌套，更细致地进行数据分组。
2. 如果在GROUP BY子句中嵌套了分组，数据将在最后指定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算，不能从个别的列取回数据。
3. GROUP BY子句中列出的每一列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式，不能使用别名。
4. 大多数SQL实现不允许GROUP BY列带有长度可变的数据类型（如文本或备注型字段）。
5. 除聚集计算语句外，SELECT语句中的每一列都必须在GROUP BY子句中给出。
6. 如果分组列中包含具有NULL值的行，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
7. GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

有的SQL实现允许根据SELECT列表中的位置指定GROUP BY的列，例如，GROUP BY 2,1可表示按选择的第二个列分组，然后再按第一个列分组。

2.2 过滤分组（HAVING）

除了能用GROUP BY分组数据外，SQL还允许过滤分组。规定包括哪些分组，排除哪些分组。例如，你可能想要列出至少有两个订单的所有顾客。为此，必须基于完整的分组而不是个别的行进行过滤。

在这个例子中WHERE不能完成任务，因为WHERE过滤指定的是行而不是分组。事实上，WHERE没有分组的概念。

那么，不使用WHERE使用什么呢？SQL为此提供了另一个子句，就是HAVING子句。HAVING非常类似于WHERE，唯一的区别是，WHERE过滤行，而HAVING过滤分组。

SELECT cust_id, COUNT(*) AS orders
FROM Orders 
GROUP BY cust_id
HAVING COUNT(*) >= 2;

HAVING过滤了COUNT(*)>=2（两个以上订单）的那些分组。

WHERE子句在这里不起作用，因为过滤是基于分组聚集值，而不是特定行的值。

WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。

SELECT vend_id, COUNT(*) AS num_prods
FROM Products 
WHERE prod_price >= 4
GROUP BY vend_id
HAVING COUNT(*) >= 2;

这条语句中，第一行是使用了聚集函数的基本SELECT语句，很像前面的例子。WHERE子句过滤所有prod_price至少为4的行，然后按vend_id分组数据，HAVING子句过滤计数为2或2以上的分组。如果没有WHERE子句，就会多检索一行（供应商DLL01，销售4个产品，价格都在4以下）。

SELECT vend_id, COUNT(*) AS num_prods
FROM Products 
GROUP BY vend_id
HAVING COUNT(*) >= 2;

注意：使用HAVING时应该结合GROUP BY子句，而WHERE子句用于标准的行级过滤。

2.3 分组和排序（GROUP BY、ORDER BY）

GROUP BY和ORDER BY经常完成相同的工作，但它们非常不同。

一般在使用GROUP BY子句时，应该也给出ORDER BY子句。这是保证数据正确排序的唯一方法，千万不要仅依赖GROUP BY排序数据。

检索包含三个或更多物品的订单号和订购物品的数目。

SELECT order_num, COUNT(*) AS items
FROM OrderItems 
GROUP BY order_num
HAVING COUNT(*) >= 3;

要按订购物品的数目排序输出，需要添加ORDER BY子句。

SELECT order_num, COUNT(*) AS items
FROM OrderItems 
GROUP BY order_num
HAVING COUNT(*) >= 3
ORDER BY items, order_num;

2.4 SELECT子句顺序

SELECT子句及其顺序

如果您发现文中有不清楚或者有问题的地方，请在下方评论区留言，我会根据您的评论，更新文中相关内容，谢谢！